﻿//using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;
using System.Xml.Linq;

namespace jinquantesthuiyuan_system
{
    public partial class HY : Form
    {
        public HY()
        {
            InitializeComponent();
            StartPosition = FormStartPosition.CenterScreen;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == string.Empty)
            {
                MessageBox.Show("用户姓名不能为空！");
                return;
            }
            try
            {
                //执行查找数据的语法
                //查找 姓名、手机号码、会员号码
                string Con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=huiyuan.accdb";
                OleDbConnection dbconn = new OleDbConnection(Con);
                dbconn.Open();//建立连接
                string sql = string.Format("select * from `jq_huiyuan` where 1=1");
                if(textBox1.Text.Trim().Length != 0)
                {
                    sql += string.Format(" and user like '%{0}%'", textBox1.Text.Trim());//and 前面必须增加空格，否则无法进行查询
                }
                //string s1 = textBox1.Text.Trim();
                OleDbDataAdapter inst = new OleDbDataAdapter(sql, dbconn);//选择全部数据
                //判断查询的数据
                //if(inst.SelectCommand != null)
                //{
                //    MessageBox.Show("不存在数据！");
                //    return;
                //}
                DataSet ds = new DataSet();//临时缓存
                inst.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                dbconn.Close();//关闭连接
            }
            catch(Exception ex)
            {
                MessageBox.Show("查询错误：" + "\r\n" + ex);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Addhy os1 = new Addhy();
            os1.StartPosition = FormStartPosition.CenterScreen;
            os1.ShowDialog();
        }

        private void HY_Load(object sender, EventArgs e)
        {
            //
            //toolStripStatusLabel2.Text = System.DateTime.Now.ToString("F");
            //去除datagridview 空白行
            dataGridView1.AllowUserToAddRows = false;

            //SqliteConnection conn = null;
            //string path = "Data Source =" + Environment.CurrentDirectory + "jq_huiyuan.s3db";
            //conn = new SqliteConnection(path);
            //conn.Open();

            //DataAdapter s2 = new DataAdapter("SELECT user, phone, hphone, zye, kye, xje, xtime FROM huiyuan"); 
            ////string sql = "SELECT user, phone, hphone, zye, kye, xje, xtime FROM huiyuan;";
            ////SqliteCommand command = new SqliteCommand(conn);
            ////command.ExecuteNonQuery();
            ////读datagridview 
            ////SqliteDataReader s2 = new SqliteDataReader("SELECT user, phone, hphone, zye, kye, xje, xtime FROM huiyuan;",sql,conn);
            ////DataSet ds = new DataSet();

            
            string Con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=huiyuan.accdb";
            try
            {
                OleDbConnection dbconn = new OleDbConnection(Con);
                dbconn.Open();//建立连接
                OleDbDataAdapter inst = new OleDbDataAdapter("SELECT * FROM jq_huiyuan", dbconn);//选择全部数据
                DataSet ds = new DataSet();//临时缓存
                inst.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                textBox11.Text = "" + dataGridView1.Rows.Count.ToString() + "     条";
                dbconn.Close();//关闭连接
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据读取错误" + "\r\n" + ex);
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            //刷新数据 这里的方法是从新整体加载数据
            string Con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=huiyuan.accdb";
            try
            {
                OleDbConnection dbconn = new OleDbConnection(Con);
                dbconn.Open();//建立连接
                OleDbDataAdapter inst = new OleDbDataAdapter("SELECT * FROM jq_huiyuan", dbconn);//选择全部数据
                DataSet ds = new DataSet();//临时缓存
                inst.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                MessageBox.Show("数据刷新成功！");
                dbconn.Close();//关闭连接
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据读取错误" + "\r\n" + ex);
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            if (textBox2.Text == string.Empty)
            {
                MessageBox.Show("手机号码不能为空！");
                return;
            }
            try
            {
                //执行查找数据的语法
                //查找 姓名、手机号码、会员号码
                string Con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=huiyuan.accdb";
                OleDbConnection dbconn = new OleDbConnection(Con);
                dbconn.Open();//建立连接
                string sql = string.Format("select * from `jq_huiyuan` where 1=1");
                if (textBox2.Text.Trim().Length != 0)
                {
                    sql += string.Format(" and phone like '%{0}%'", textBox2.Text.Trim());//and 前面必须增加空格，否则无法进行查询
                }
                //string s1 = textBox1.Text.Trim();
                OleDbDataAdapter inst = new OleDbDataAdapter(sql, dbconn);//选择全部数据
                DataSet ds = new DataSet();//临时缓存
                inst.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                dbconn.Close();//关闭连接
            }
            catch (Exception ex)
            {
                MessageBox.Show("查询错误：" + "\r\n" + ex);
            }
        }

        private void 刷新数据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //右键刷新数据
            //textBox11.Text = "" + dataGridView1.Rows.Count.ToString();
            string Con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=huiyuan.accdb";
            try
            {
                OleDbConnection dbconn = new OleDbConnection(Con);
                dbconn.Open();//建立连接
                OleDbDataAdapter inst = new OleDbDataAdapter("SELECT * FROM jq_huiyuan", dbconn);//选择全部数据
                DataSet ds = new DataSet();//临时缓存
                inst.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                textBox11.Text = "" + dataGridView1.Rows.Count.ToString() + "     条";
                //MessageBox.Show("数据刷新成功！");
                dbconn.Close();//关闭连接
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据读取错误" + "\r\n" + ex);
            }
        }

        private void 修改数据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //右键修改数据
            this.dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;//设置整行被选中
            DataGridViewRow drv = this.dataGridView1.CurrentRow;
            if (drv != null)
            {
                DataRow dr = (drv.DataBoundItem as DataRowView).Row;
                //HyEdit s1s = new HyEdit(dr);
                HyEdit f1 = new HyEdit(dr);
                f1.StartPosition = FormStartPosition.CenterScreen;//居中显示
                f1.ShowDialog();
            }
            else
            {
                MessageBox.Show("请选中一行数据");
            }
        }

        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            //进行自动的排序
            //var dgv = (DataGridView)sender;
            //if (dgv.RowHeadersVisible)
            //{
            //    Rectangle rectangle = new Rectangle(e.RowBounds.Left, e.RowBounds.Top, e.RowBounds.Width, e.RowBounds.Height);
            //    rectangle.Inflate(-3, 0);
            //    TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(), e.InheritedRowStyle.Font, rectangle, e.InheritedRowStyle.ForeColor, TextFormatFlags.Left | TextFormatFlags.VerticalCenter);
            //}
        }

        private void button3_Click(object sender, EventArgs e)
        {

        }

        private void 删除数据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                int i = dataGridView1.CurrentRow.Index;
                string sql = dataGridView1.Rows[i].Cells["ID"].Value.ToString();//获取选中的行
                DialogResult res = MessageBox.Show("确定要删除" + sql + "列表中所选的一行吗？", "删除提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                if (res == DialogResult.OK)  //按确定才进行下一步删除
                {
                    string Con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=huiyuan.accdb";
                    OleDbConnection dbconn = new OleDbConnection(Con);
                    dbconn.Open();//建立连接

                    string s1 = "DELETE FROM `jq_huiyuan` WHERE ID=" + sql;//注意字符串的拼接，与其它的数据库方式不一样
                    OleDbCommand command = new OleDbCommand(s1, dbconn);
                    //command.ExecuteNonQuery();
                    int des = command.ExecuteNonQuery();
                    if (des > 0)//判断删除的状态
                    {
                        MessageBox.Show("删除成功！");
                        OleDbDataAdapter inst = new OleDbDataAdapter("SELECT * FROM jq_huiyuan", dbconn);//选择全部数据
                        DataSet ds = new DataSet();//临时缓存
                        inst.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                        textBox11.Text = "" + dataGridView1.Rows.Count.ToString() + "     条";
                        dbconn.Close();//关闭连接
                    }
                    else
                    {
                        MessageBox.Show("删除失败！");
                    }
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show("错误提示：" + "\r\n" + ex);
            }
        }
        protected void updates()
        {
            string Con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=huiyuan.accdb";
            try
            {
                OleDbConnection dbconn = new OleDbConnection(Con);
                dbconn.Open();//建立连接
                OleDbDataAdapter inst = new OleDbDataAdapter("SELECT * FROM jq_huiyuan", dbconn);//选择全部数据
                DataSet ds = new DataSet();//临时缓存
                inst.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                MessageBox.Show("数据刷新成功！");
                dbconn.Close();//关闭连接
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据刷新错误" + "\r\n" + ex);
            }
        }

        private void button3_Click_1(object sender, EventArgs e)
        {
            if (textBox3.Text == string.Empty)
            {
                MessageBox.Show("会员号码不能为空！");
                return;
            }
            try
            {
                //执行查找数据的语法
                //查找 姓名、手机号码、会员号码
                string Con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=huiyuan.accdb";
                OleDbConnection dbconn = new OleDbConnection(Con);
                dbconn.Open();//建立连接
                string sql = string.Format("select * from `jq_huiyuan` where 1=1");
                if (textBox3.Text.Trim().Length != 0)
                {
                    sql += string.Format(" and hpone like '%{0}%'", textBox3.Text.Trim());//and 前面必须增加空格，否则无法进行查询
                }
                //string s1 = textBox1.Text.Trim();
                OleDbDataAdapter inst = new OleDbDataAdapter(sql, dbconn);//选择全部数据
                DataSet ds = new DataSet();//临时缓存
                inst.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                dbconn.Close();//关闭连接
            }
            catch (Exception ex)
            {
                MessageBox.Show("查询错误：" + "\r\n" + ex);
            }
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            //单选某行datagridview 进行显示 点击事件
            if (dataGridView1.CurrentRow.Selected)
            {
                int i = dataGridView1.CurrentRow.Index;
                textBox4.Text = dataGridView1.Rows[i].Cells["姓名"].Value.ToString();
                textBox5.Text = dataGridView1.Rows[i].Cells["手机号码"].Value.ToString();
                textBox6.Text = dataGridView1.Rows[i].Cells["会员号码"].Value.ToString();
                textBox7.Text = dataGridView1.Rows[i].Cells["总余额"].Value.ToString();
                textBox8.Text = dataGridView1.Rows[i].Cells["可用余额"].Value.ToString();
                textBox9.Text = dataGridView1.Rows[i].Cells["消费金额"].Value.ToString();
                textBox10.Text = dataGridView1.Rows[i].Cells["消费时间"].Value.ToString();
                //dateTimePicker1.Text = dataGridView1.Rows[i].Cells["消费时间"].Value.ToString();
            }
        }

        private void button7_Click(object sender, EventArgs e)
        {
            //修改保存数据
            try
            {
                int i = dataGridView1.CurrentRow.Index;
                string sql = dataGridView1.Rows[i].Cells["ID"].Value.ToString();//获取选中的行
                DialogResult res = MessageBox.Show("确定要修改" + sql + "列表中所选的一行吗？", "修改提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                if (res == DialogResult.OK)  //按确定才进行下一步删除
                {
                    string Con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=huiyuan.accdb";
                    OleDbConnection dbconn = new OleDbConnection(Con);
                    dbconn.Open();//建立连接

                    string s2 = textBox4.Text;
                    //string s1 = $"UPDATE `jq_huiyuan` SET `user`='" + s2 + "' where ID=''" + sql;//注意字符串的拼接，与其它的数据库方式不一样
                    string s1 = "update `jq_huiyuan` set `user` = '" + textBox4.Text + "', `phone` = '" + textBox5.Text + "', `zye` = '" + textBox7.Text + "', `hpone` = '" + textBox6.Text + "', `xje` = '" + textBox9.Text + "', `kye` = '" + textBox8.Text + "', `xtime` = '" + textBox10.Text + "' where Id = " + sql;
                    //UPDATE `jq_huiyuan` SET `user`='' WHERE ID=
                    //string s1 = "update `jq_huiyuan` set `user` = '" + textBox4.Text + "', `xtime` = '" + textBox10.Text + "' where Id = " + sql;
                    OleDbCommand command = new OleDbCommand(s1, dbconn);
                    //command.ExecuteNonQuery();
                    int des = command.ExecuteNonQuery();
                    if (des > 0)//判断删除的状态
                    {
                        MessageBox.Show("修改成功！");
                        OleDbDataAdapter inst = new OleDbDataAdapter("SELECT * FROM jq_huiyuan", dbconn);//选择全部数据
                        DataSet ds = new DataSet();//临时缓存
                        inst.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0];
                        textBox11.Text = "" + dataGridView1.Rows.Count.ToString() + "     条";
                        dbconn.Close();//关闭连接
                    }
                    else
                    {
                        MessageBox.Show("修改失败！");
                    }
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show("错误提示：" + "\r\n" + ex);
            }
        }

        private void toolStripStatusLabel2_Click(object sender, EventArgs e)
        {

        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            toolStripStatusLabel2.Text = System.DateTime.Now.ToString("F");
        }

        private void 修改密码ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //点击后可进行密码的修改
            EditPass os = new EditPass();
            os.StartPosition = FormStartPosition.CenterScreen;
            os.ShowDialog();
        }

        private void 退出系统ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void 增加会员ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Addhy os1 = new Addhy();
            os1.StartPosition = FormStartPosition.CenterScreen;
            os1.ShowDialog();
        }
    }
}
